<?php
class OraclesController extends AppController {

	var $name = 'Oracles';

	function beforeRender() {
		$groupid = $this->Auth->user('group_id');
		$name = 'Oracles';
		IF($groupid != 1){
			$result1 = mysql_query("SELECT id FROM applications WHERE name = '$name'");
			$appid = mysql_fetch_object($result1)->id;
			$result2 = mysql_query("SELECT count(*) as count FROM groupshasrights WHERE group_id = '$groupid' AND application_id = '$appid'");
			$count = mysql_fetch_object($result2)->count;
			IF($count == 0){
				$this->Session->setFlash(__('Access Denied', true));
				$this->redirect(array('controller' => 'pages' ,'action' => 'error'));	
			}
		}
	}
	
	function findxconnect($account){
		$sql = "SELECT TSP.SERIAL_NO
 FROM
   SACH.TABLE_CONTACT TC,
   SACH.TABLE_CONTRACT TCR,
   SACH.TABLE_CONTR_SCHEDULE TCS, 
   SACH.TABLE_CONTR_ITM TCI,
   SACH.TABLE_SITE_PART TSP,
   SACH.TABLE_MOD_LEVEL TML,
   SACH.TABLE_PART_NUM TPN,
   OSSNICH.CR_TEL_X_CONNECT TXC
 WHERE 
   TC.OBJID = TCR.PRIMARY2CONTACT
   AND TCR.OBJID = TCS.SCHEDULE2CONTRACT 
   AND TCS.OBJID = TCI.CONTR_ITM2CONTR_SCHEDULE
   AND TCI.CONTR_ITM2SITE_PART = TSP.OBJID
   AND TSP.SITE_PART2PART_INFO = TML.OBJID
   AND TML.PART_INFO2PART_NUM = TPN.OBJID
   AND TSP.LEVEL_TO_PART NOT IN (999)
   AND TSP.INSTANCE_NAME IN ('PT - 1026','PT - 1014')
   AND TXC.tel_num = TSP.SERIAL_NO
   AND TC.X_ACCNO = '$account'";
	    
		$xvoip = $this->Oracle->query($sql);
	    if(!empty($xvoip)){ return 1; }
	    else { return 0; }
	    
	}
	
	function findPR($account){
		$sql = "SELECT COUNT(*) AS COUNT FROM SACH.TABLE_DEMAND_DTL TDD,SACH.TABLE_CONTACT TCO,SACH.TABLE_DEMAND_HDR TDH WHERE TDD.DEMAND_DTL2DEMAND_HDR = TDH.OBJID AND TDH.ATTENTION2CONTACT = TCO.OBJID AND TDD.DMND_DTL_STS2GBST_ELM NOT IN('268437848','268437672','268436960','268436957','268437812','268435875','268435869','268435479','268435868','268437713') AND TCO.X_ACCNO = '$account'";
	    $counter = $this->Oracle->query($sql);
	    return $counter[0]['0']['COUNT'];		
	}
	
	
	function findDetails($id){
		$sql = "SELECT WEB_ORDER_ID, CUST_ID, CASE_ID, PART_REQUEST_ID FROM SACH.table_x_web_clarify WHERE web_order_id = '$id'";
		$result = $this->Oracle->query($sql);
		return $result;	
	}
	
	function getModemStatus(){
		$mac = $this->params['url']['mac'];
		$this->layout = null;
        $this->autoLayout = false;
		$sql = "SELECT TPI.X_PART_STATUS STATUS FROM SACH.TABLE_PART_NUM TPN, SACH.TABLE_MOD_LEVEL TML, SACH.TABLE_PART_INST TPI, SACH.TABLE_X_INVENT_ATTR TXI WHERE TPN.OBJID = TML.PART_INFO2PART_NUM AND TPI.N_PART_INST2PART_MOD = TML.OBJID AND TPI.OBJID = TXI.X_INVENT_ATTR2PART_INST AND TXI.X_ID = '$mac'";
		$result = $this->Oracle->query($sql);
		if(!empty($result)){
			$daten = $result[0]['TPI']['status'];		
	    }
	    else {
	    	$daten = "no info";
	    }	
	    $this->set(compact('daten'));
	}
	
	
	function findInstance($serial){
		$sql = "SELECT TSP.SERIAL_NO, TSP.INSTANCE_NAME FROM SACH.TABLE_SITE_PART TSP WHERE TSP.LEVEL_TO_PART NOT IN (999) START WITH TSP.S_SERIAL_NO = '$serial' CONNECT BY PRIOR TSP.OBJID = TSP.SITE_PART2SITE_PART AND TSP.INSTANCE_NAME IN ('DMM - SC','DMM - SC2')";
		$result = $this->Oracle->query($sql);
		return $result;
	}
	
}
?>